-- @owner: zou_jialiang050
-- @date: 2024/4/2
-- @testpoint: 关闭自治事务执行DML操作,部分测试点合理报错

--step1:创建嵌套表类型;expect:成功
create or replace type tb_type_0013 as table of varchar2(2000);

--step2:关闭自治事务执行DML操作;expect:失败
drop table if exists t_pipelined_0013;
create table t_pipelined_0013(c1 int);
create or replace function func_pipelined_0013(count in number)
returns tb_type_0013 pipelined
as $$
declare
begin
for i in 1 .. count loop
    insert into t_pipelined_0013 values(i);
    perform pg_sleep(1);
    update t_pipelined_0013 set c1 = 10 where c1 = i;
end loop;
    pipe row( 'All done!' );
return;
end;
$$ language plpgsql;
/

CREATE OR REPLACE PACKAGE pkg0013 AS
    TYPE array_type_0013_1 AS varray(10) OF char(10);
    FUNCTION func_pipelined_0013_1(count NUMBER) RETURN array_type_0013_1 pipelined;
END pkg0013;
/

CREATE OR REPLACE PACKAGE BODY pkg0013 AS
    FUNCTION func_pipelined_0013_1(count NUMBER) RETURN array_type_0013_1 pipelined IS
	declare result array_type_0013_1;
        BEGIN
            FOR i IN 1..count LOOP
                insert into t_pipelined_0013 values(i);
                perform pg_sleep(1);
                update t_pipelined_0013 set c1 = 10 where c1 = i;
            end loop;
                pipe row( 'All done!' );
            RETURN;
        END;
END pkg0013;
/

--step3:调用pipelined函数并查看表数据;expect:成功
select func_pipelined_0013(3);
select * from func_pipelined_0013(3);
select * from table(func_pipelined_0013(3));
select pkg0013.func_pipelined_0013_1(2);

--step4:清理环境;expect:成功
drop function if exists func_pipelined_0013;
drop type if exists tb_type_0013 cascade;
drop package if exists pkg0013 cascade;
